package de.hsbremen.dirtybox;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * This class builds up the SQLite database behind the
 * application. It consists of for tables:
 * - symbol, which stores all symbols
 * - category, which stores the categories
 * - list, which stores the lists
 * - symbol2list, which breaks up the m:n relationship
 * between symbols and lists.
 *
 * All tables are created with raw SQL execution commands.
 *
 * @author Marcus Fischer
 */
public final class DirtySQLiteDatabase extends SQLiteOpenHelper {

	private static final int DATABASE_VERSION = 1;
	private static final String DATABASE_NAME = "DirtySQLiteDatabase";

	private static final String CREATE_TABLE_FOLDER = "CREATE TABLE folder ("
			+ "_ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "
			+ "FolderName VARCHAR(255) NOT NULL UNIQUE, "
			+ "ParentFolderID INTEGER REFERENCES folder(_ID) ON DELETE CASCADE DEFAULT 1"
			+ ");";

	private static final String CREATE_TABLE_IMAGE = "CREATE TABLE image ("
			+ "_ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "
			+ "ImageData BLOB NOT NULL, "
			+ "ParentFolderID INTEGER REFERENCES folder(_ID) ON DELETE CASCADE DEFAULT 1"
			+ ");";

	/**
	 * The constructor, giving the context, the actual database
	 * name as well as the version via super command further.
	 * @param context
	 */
	DirtySQLiteDatabase(final Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(final SQLiteDatabase db) {
		db.execSQL(CREATE_TABLE_FOLDER);
		db.execSQL(CREATE_TABLE_IMAGE);
		db.execSQL("INSERT INTO folder (FolderName) VALUES ('Root');");
	}

	@Override
	public void onUpgrade(final SQLiteDatabase db, final int oldVersion,
			final int newVersion) {
		// Do nothing special ... No database upgrades considered yet!
	}
}
